using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * Autor      : IT-Developer1
 * Data       : 02/04/2010 11:03:48
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
    public class CProgramasMunicipios
    {
        private string IDT_PROG;
        private string IDT_MUN;

        /// <sumary>
        /// Construtor da classe programas_municipios
        /// <sumary>
        public CProgramasMunicipios()
        {

        }

        /// <sumary>
        /// Construtor da classe Programas_municipios
        /// <sumary>
        public CProgramasMunicipios(string IDT_PROG, string IDT_MUN)
        {
            this.IDT_PROG = IDT_PROG;
            this.IDT_MUN = IDT_MUN;
        }

        #region Propriedades
        /// <sumary>
        /// Identificador do programa
        /// <sumary>
        public string Idt_prog
        {
            get
            {
                return this.IDT_PROG;
            }
            set
            {
                this.IDT_PROG = value;
            }
        }
        /// <sumary>
        /// Identificador do municipio
        /// <sumary>
        public string Idt_mun
        {
            get
            {
                return this.IDT_MUN;
            }
            set
            {
                this.IDT_MUN = value;
            }
        }
        #endregion Propriedades

        /// <summary>
        /// Alterando um Programas_municipios.
        /// </summary>
        public static void PermissoesEmLote(int idt_prog, List<int> idts_mun, int pms_ANO, string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL_DELETE = @"DELETE FROM Serix.SRX_PROGRAMAS_MUNICIPIOS 
                                   WHERE IDT_PROG = :IDT and PMS_ANO = :PMS_ANO";
            
            string SQL_INSERT = @"INSERT INTO Serix.SRX_PROGRAMAS_MUNICIPIOS 
                                   (IDT_PROG, PMS_ANO, IDT_MUN) VALUES (:IDT, :PMS_ANO, :MUN)";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL_DELETE;

                command.Parameters.Add(new OracleParameter("IDT", OracleType.Number));
                command.Parameters.Add(new OracleParameter("PMS_ANO", OracleType.Number));
                command.Parameters["IDT"].Value = idt_prog;
                command.Parameters["PMS_ANO"].Value = pms_ANO;

                command.ExecuteNonQuery();
                command.Parameters.Clear();

                command.CommandText = SQL_INSERT;

                command.Parameters.Add(new OracleParameter("IDT", OracleType.Number));
                command.Parameters.Add(new OracleParameter("PMS_ANO", OracleType.Number));
                command.Parameters["IDT"].Value = idt_prog;
                command.Parameters["PMS_ANO"].Value = pms_ANO;

                command.Parameters.Add(new OracleParameter("MUN", OracleType.Number));
                foreach (int mun in idts_mun)
                {
                    command.Parameters["MUN"].Value = mun;
                    command.ExecuteNonQuery();
                }

                command.Transaction.Commit();                
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        /// <summary>
        /// Inserindo um novo Programas_municipios.
        /// </summary>
        public void Inserir(string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_PROGRAMAS_MUNICIPIOS
			                  ( IDT_PROG, IDT_MUN )
						   values
						      ( :IDT_PROG, :IDT_MUN )";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_MUN", OracleType.Number));

                command.Parameters["IDT_PROG"].Value = this.IDT_PROG;
                command.Parameters["IDT_MUN"].Value = this.IDT_MUN;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Alterando um Programas_municipios.
        /// </summary>
        public void Alterar(string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_PROGRAMAS_MUNICIPIOS set 
			                   
						   where
						       IDT_PROG = :IDT_PROG, IDT_MUN = :IDT_MUN ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_MUN", OracleType.Number));

                command.Parameters["IDT_PROG"].Value = this.IDT_PROG;
                command.Parameters["IDT_MUN"].Value = this.IDT_MUN;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Exclui um  Programas_municipios.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_PROGRAMAS_MUNICIPIOS", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_PROG", DbType.Int32, this.IDT_PROG));
            tab.adicionarFiltro(new Coluna("IDT_MUN", DbType.Int32, this.IDT_MUN));
            tab.delete();
        }

        static public DataTable ObterIdtsMun(string idt_prog)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"SELECT A.IDT_MUN FROM Serix.SRX_PROGRAMAS_MUNICIPIOS A
                            JOIN Serix.SRX_MUNICIPIOS B ON (A.IDT_MUN = B.IDT_MUN)
                            WHERE A.IDT_PROG = :IDT";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT", OracleType.Number));
                command.Parameters["IDT"].Value = idt_prog;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;

            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        static public DataTable ObterTodos()
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_PROGRAMAS_MUNICIPIOS order by IDT_PROG ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;

            }
            catch (Exception e)
            {

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        static public CProgramasMunicipios PesquisaporCodigo(string idt_prog, string idt_mun, string login)
        {
            CProgramasMunicipios obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_PROGRAMAS_MUNICIPIOS 
                            WHERE IDT_PROG = :IDT_PROG, IDT_MUN = :IDT_MUN";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro(":IDT_PROG", DbType.String, idt_prog);
                qy.AdicionarParametro(":IDT_MUN", DbType.String, idt_mun);

                qy.Executar();
                obj = new CProgramasMunicipios(qy.ObterCampo("IDT_PROG"), qy.ObterCampo("IDT_MUN"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CProgramasMunicipios();
            }
            finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

        static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_PROGIDT_MUN() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.Executar();
                return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return -1;
            }
            finally
            {
                qy.FecharConexao();
            }
        }

        static public DataTable ObterPorIDT_PROG(string idt_prog)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_PROGRAMAS_MUNICIPIOS  where IDT_PROG = :IDT_PROG order by IDT_PROG ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters["IDT_PROG"].Value = idt_prog;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }


        static public DataTable ObterTodosPorIDT_PROG(string idt_prog, int pms_ANO)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"
select muni.idt_mun, nome_mun
      ,CAST(
        (case when idt_prog  is null  then 0 else 1
        end ) as int ) as Programa
    ,idt_prog
    from Serix.SRX_MUNICIPIOS muni
  left join Serix.SRX_PROGRAMAS_MUNICIPIOS proMuni on muni.IDT_MUN = promuni.idt_mun and IDT_PROG = :IDT_PROG and proMuni.PMS_ANO = :PMS_ANO
order by nome_mun";
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_PROG", OracleType.Number));
                command.Parameters.Add(new OracleParameter("PMS_ANO", OracleType.Number));
                command.Parameters["IDT_PROG"].Value = idt_prog;
                command.Parameters["PMS_ANO"].Value = pms_ANO;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }





        static public DataTable ObterPorIDT_MUN(string idt_mun)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_PROGRAMAS_MUNICIPIOS  where IDT_MUN = :IDT_MUN order by IDT_PROG ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_MUN", OracleType.Number));
                command.Parameters["IDT_MUN"].Value = idt_mun;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
    }
}

